package com.mes.cloud.utils;

import com.google.common.collect.Maps;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
/**
 * sss
 *
 * @Author:WenJun Fan
 * @Date: 2018/5/31
 */
/**
 * 操作Excel表格的功能类
 */
public class ExcelReaderUtils {

    /**
     * 读取Excel表格表头的内容
     * @param inputStream
     * @return String 表头内容的数组
     */
    public static String[] readExcelTitle(InputStream inputStream) {
        String[] title = null;
        Sheet sheet = null;
        Row row = null;
        try {
            Workbook wb = null;
            if(! inputStream.markSupported()) {
                inputStream = new PushbackInputStream(inputStream, 8);
            }
            if(POIFSFileSystem.hasPOIFSHeader(inputStream)) {
                wb = new HSSFWorkbook(inputStream);
            }
            if(POIXMLDocument.hasOOXMLHeader(inputStream)) {
                wb = new XSSFWorkbook(inputStream);
            }

            sheet = wb.getSheetAt(0);
            row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            title = new String[colNum];
            for (int i = 0; i < colNum; i++) {
                Cell cell = row.getCell(i);
                title[i] = cell.getStringCellValue();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return title;
    }


    /**
     * 读取Excel所有的sheet页名字
     * @param inputStream
     * @return String sheet内容的数组
     */
    public static String[] readExcelSheetName(InputStream inputStream) {
        String[] sheetName = null;
        Sheet sheet = null;
        try {
            Workbook wb = null;
            if(! inputStream.markSupported()) {
                inputStream = new PushbackInputStream(inputStream, 8);
            }
            if(POIFSFileSystem.hasPOIFSHeader(inputStream)) {
                wb = new HSSFWorkbook(inputStream);
            }
            if(POIXMLDocument.hasOOXMLHeader(inputStream)) {
                wb = new XSSFWorkbook(inputStream);
            }
            int maxSheet=wb.getNumberOfSheets();
            sheetName=new String[maxSheet];
            for (int i = 0; i < maxSheet; i++) {
                sheet=wb.getSheetAt(i);
                sheetName[i]=sheet.getSheetName();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sheetName;
    }

    public static void main(String[] args) {
        //Map<Integer, List<String>> tmp=excle.readExcelContent(new File("c:/test.xls"),74,31);
//        Map<Integer, List<String>> tmp = ExcelReaderUtils.readExcelContent(new File("C:\\Users\\pact.PACT-20170616CE\\Desktop\\model.xls"),74,31);
//        Object[] obj=tmp.keySet().toArray();
//        Arrays.sort(obj);
//        for (int i = 1; i < obj.length; i++) {
//            System.out.println(tmp.get(obj[i]).toString());
//        }
        String path = "C:\\Users\\pact.PACT-20170616CE\\Desktop\\合约规划类型.xlsx";
        try {
            FileInputStream fileInputStream = new FileInputStream(path);

            String[] strings = ExcelReaderUtils.readExcelTitle(new FileInputStream(path));
            if(strings != null){
                for (String string : strings) {
                    System.out.println(string);
                }
            }
            String[] strings1 = ExcelReaderUtils.readExcelSheetName(new FileInputStream(path));
            if(strings1 != null){
                for (String s : strings1) {
                    System.out.println(s);
                }
            }
            Map<Integer, List<String>> listMap = ExcelReaderUtils.readExcelContent(new FileInputStream(path), 2);
            if(listMap != null){
                for (Integer i : listMap.keySet()){
                    System.out.println(i + "====" + listMap.get(i));
                }
            }
            Map<Integer, List<String>> listMap1 = ExcelReaderUtils.readExcelContent(new File(path), 2000, 1000, 2, 0);
            if(listMap1 != null){
                for (Integer i : listMap1.keySet()){
                    System.out.println(i + "====" + listMap1.get(i));
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }


    }

    /**
     * 读取Excel数据内容
     * @param inputStream
     * @return Map 包含单元格数据内容的Map对象
     */
    public static Map<Integer, List<String>> readExcelContent(InputStream inputStream, int startRow) {
        Map<Integer, List<String>> content = Maps.newHashMap();
        Sheet sheet = null;
        Row row = null;
        try {
            Workbook wb = null;
            if(! inputStream.markSupported()) {
                inputStream = new PushbackInputStream(inputStream, 8);
            }
            if(POIFSFileSystem.hasPOIFSHeader(inputStream)) {
                wb = new HSSFWorkbook(inputStream);
            }
            if(POIXMLDocument.hasOOXMLHeader(inputStream)) {
                wb = new XSSFWorkbook(inputStream);
            }
            sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum()+1;
            row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = startRow; i < rowNum; i++) {
                row = sheet.getRow(i);
                if(row==null){
                    break;
                }
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < colNum; j++) {

                    Cell cell = row.getCell(j);
                    if(cell != null) {
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    //如果是date类型则 ，获取该cell的date值
                                    list.add(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString());
                                } else { // 纯数字
                                    double d = cell.getNumericCellValue();
                                    if (d - (int) d < Double.MIN_VALUE) {
                                        // 是否为int型
                                        list.add(Integer.toString((int) d));
                                    } else {
                                        // 是否为double型
                                        list.add(Double.toString(cell.getNumericCellValue()));
                                    }
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                list.add(cell.getStringCellValue() + "");
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                list.add(cell.getBooleanCellValue() + "");
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                list.add(cell.getCellFormula() + "");
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空值
                                list.add("");
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 故障
                                list.add("");
                                break;
                            default:
                                list.add("");
                                break;
                        }
                    } else {
                        list.add("");
                    }
                }
                content.put(i, list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return content;
    }


    /**
     * 读取Excel数据内容  限制取多少个Cell  从0开始算
     *
     * @param maxRow  想要获取excel的最大长度
     * @return Map 包含单元格数据内容的Map对象
     */
    public static Map<Integer, List<String>> readExcelContent(File file,int maxRow,int maxCell) {
        return readExcelContent(file, maxRow, maxCell, 1);
    }


    /**
     * 读取Excel数据内容  限制取多少个Cell  从0开始算
     *
     * @param maxRow  想要获取excel的最大长度
     * @return Map 包含单元格数据内容的Map对象
     */
    public static Map<Integer, List<String>> readExcelContent(File file,int maxRow,int maxCell,int startRow) {
        return readExcelContent(file, maxRow, maxCell, startRow, 0);
    }


    /**
     *
     * @param file  文件
     * @param maxRow 最大行 -1为不限制
     * @param maxCell  最大列  -1为不限制
     * @param startRow 开始行
     * @param sheetIndex  第几个sheet页
     * @return
     */
    public static Map<Integer, List<String>> readExcelContent(File file,int maxRow,int maxCell,int startRow,int sheetIndex) {
        Sheet sheet = null;
        Row row = null;
        Map<Integer, List<String>> content = new HashMap<Integer, List<String>>();
        try {
            Workbook wb = null;
            try{
                wb = new HSSFWorkbook(new FileInputStream(file));
            }catch(OfficeXmlFileException e){
                wb = new XSSFWorkbook(new FileInputStream(file));
            }
            sheet = wb.getSheetAt(sheetIndex);
            // 得到总行数、总列数
            int rowNum = sheet.getLastRowNum()+1;
            row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            if(maxRow==-1){
                maxRow=rowNum;
            }
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = startRow; i < maxRow; i++) {
                row = sheet.getRow(i);
                if(row==null){
                    break;
                }
                if(row.getZeroHeight()==true)
                {
                    System.out.println("row("+(i++)+")是隐藏的");
                    continue;
                }
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < colNum; j++) {
                    if(maxCell!=-1&&j>maxCell){//如限制行数
                        break;
                    }
                    Cell cell = row.getCell(j);

                    if(cell != null) {

                        boolean isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
                        if(isMerge) {
                            String rs = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
                            list.add(rs);
                        }else{
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        //如果是date类型则 ，获取该cell的date值
                                        list.add(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString());
                                    } else { // 纯数字
                                        double d = cell.getNumericCellValue();
                                        if (d - (int) d < Double.MIN_VALUE) {
                                            // 是否为int型
                                            list.add(Integer.toString((int) d));
                                        } else {
                                            // 是否为double型
                                            list.add(Double.toString(cell.getNumericCellValue()));
                                        }
                                    }
                                    break;
                                case HSSFCell.CELL_TYPE_STRING: // 字符串
                                    list.add(cell.getStringCellValue() + "");
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                    list.add(cell.getBooleanCellValue() + "");
                                    break;
                                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                    list.add(cell.getCellFormula() + "");
                                    break;
                                case HSSFCell.CELL_TYPE_BLANK: // 空值
                                    list.add("");
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR: // 故障
                                    list.add("");
                                    break;
                                default:
                                    list.add("");
                                    break;
                            }

                        }

                    } else {
                        list.add("");
                    }
                }
                content.put(i, list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return content;
    }



    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private static String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }


    /**
     * 是否为单元格
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }


    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }


    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){

        if(cell == null) return " ";

        if(cell.getCellType() == Cell.CELL_TYPE_STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

            return String.valueOf(cell.getNumericCellValue());

        }
        return " ";
    }


    /***
     * @methodName getInputStreamFromRequest
     * @date 2018/5/31 15:47
     * @param request
     * @return java.io.InputStream
     * 从请求中获取流信息
     */
    public static InputStream getInputStreamFromRequest(HttpServletRequest request){
        InputStream is=null;
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
        if(multipartResolver.isMultipart(request)){
            MultipartHttpServletRequest multiRequest=(MultipartHttpServletRequest)request;
            Iterator<String> iterator = multiRequest.getFileNames();
            while (iterator.hasNext()) {
                MultipartFile multipartFile = multiRequest.getFile(iterator.next());
                if(multipartFile !=null){
                    try {
                        is=new ByteArrayInputStream(multipartFile.getBytes());
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return is;
    }

    /***
     * @methodName getInputStreamFromMultipartFile
     * @date 2018/5/31 15:47
     * @param files
     * @return java.util.list
     * 从请求中获取流信息
     */
    public static List<InputStream> getInputStreamFromMultipartFile(MultipartFile[] files){
        List<InputStream> list = new ArrayList<>();
        if(files != null && files.length > 0) {
            for (MultipartFile file : files) {
                if (file != null) {
                    try {
                        InputStream is = new ByteArrayInputStream(file.getBytes());
                        list.add(is);
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return list;
    }
}
